******************************************************************************** 
* import lending rate sand perpetual futures
********************************************************************************
use "build/output/main_panel.dta", clear
tsset coin_id date

keep if coin == "USDT"

collapse (mean) funding_apr margin_aprFTX, by(qdate)

tempfile tf
save `tf'

******************************************************************************** 
* import tether balance sheet data
********************************************************************************
import excel "build/input/reserves/Stablecoin Reserves.xlsx", sheet("Sheet1") firstrow clear

gen liquid_share =  ((CashAndBankDeposits+ReverseRepo+TreasuryBills)/Total)*100

gen qdate = qofd(ReportDate)
format qdate %tq

keep qdate liquid_share  


******************************************************************************** 
* clean
********************************************************************************
merge 1:m qdate using `tf', nogen keep(match)


******************************************************************************** 
* scatter plot: for BTCUSDT
********************************************************************************
format margin_apr  %10.0fc
format liquid_share %10.0fc

tempfile y
tempfile R

tw (scatter funding_apr liquid_share, xlabel(20(10)80) ///
	ytitle("") ///
	subtitle("(a) Perpetual Futures Funding Rate (%, Annualized)",  orientation(horizontal)  bexpand justification(left)) ///
	xtitle("Liquid Share of Portfolio (%)") legend(ring(0) position(6) rows(1)) ///
	mlabel(qdate) mlabp(6) mlabsize(small))   ///
	(lfit funding_apr liquid_share, lcolor("0 114 178") mlabel(qdate) mlabp(10) mlabsize(small) ) ///
	, saving(`y'.gph, replace) legend(off) 

tw (scatter margin_apr liquid_share, xlabel(20(10)80) ///
	ytitle("") ///
	subtitle("(b) USDT Margin Lending Rate (%, Annualized)",  orientation(horizontal)  bexpand justification(left)) ///
	xtitle("Liquid Share of Portfolio (%)") legend(ring(0) position(6) rows(1)) ///
	mlabel(qdate) mlabp(9) mlabsize(small)) ///
	(lfit margin_apr liquid_share, lcolor("0 114 178")), ///
	saving(`R'.gph, replace) legend(off)

gr combine `y'.gph `R'.gph, scale(*1.25)
gr export "analysis/output_figures/figure_balancesheet_scatter.pdf", replace
